//
//  DataBaseManager.m
//  UI23_FMDB
//
//  Created by dllo on 16/3/14.
//  Copyright © 2016年 LanOu. All rights reserved.
//

#import "DataBaseManager.h"
#import "ObjectTool.h"
@implementation DataBaseManager
// 单例方法
+ (DataBaseManager*)shareInstance{
    static DataBaseManager *dbManager = nil;
    if (!dbManager) {
        dbManager = [[DataBaseManager alloc] init];
    }
    return dbManager;
}

// 打开数据库
- (void)openDB{
    // Documents路径
    NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    // 判断文件夹是否存在
    NSString *filePath = [path stringByAppendingPathComponent:@"db"];
    if(![[[NSFileManager defaultManager] contentsOfDirectoryAtPath:filePath error:nil] count]){
        [[NSFileManager defaultManager] createDirectoryAtPath:filePath withIntermediateDirectories:YES attributes:nil error:nil];
    }
    // 拼接数据库路径 (db/sqlite)如果设置其他 无法用MesaSQLite打开
    self.filePath = [path stringByAppendingPathComponent:@"db/table.db"];
    
    // 根据数据库路径创建数据库对象
    self.db = [[FMDatabase alloc] initWithPath:_filePath];
    // 打开数据库
    BOOL result = [_db open];
    if (result) {
        NSLog(@"打开数据库成功");
    }else {
        NSLog(@"打开数据库失败");
    }
}
// 关闭数据库
- (void)closeDB{
    [_db close];
}

#pragma mark
#pragma mark ====> 音频下载表
//-(void)createAudioDownloadTable{
//    NSString *createSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS AudioDownloadTable(id INTEGER PRIMARY KEY AUTOINCREMENT, userid TEXT, courseid TEXT, courseName TEXT,coursePic TEXT, mediaid TEXT, mediaName TEXT)"];
//    [self executeSQL:createSQL message:@"创建表"];
//}
//
//-(BOOL)insertAudionWithCourse:(LGFollwCourseModel *)course mediaModel:(LGFollowMediaModel *)model{
//    NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO AudioDownloadTable(userid, courseid, courseName, coursePic, mediaid, mediaName) VALUES('%@','%@','%@','%@','%@','%@')",kUserId, course.ano,course.title,course.pic,model.aunid,model.title];
//    return [self executeSQL:insertSQL message:@"INSERT AudioDownloadTable"];
//}
//
//-(NSMutableArray *)selectDownloadAudios{
//    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM AudioDownloadTable  WHERE userid = '%@'",kUserId];
//    FMResultSet *set = [_db executeQuery:selectSQL];
//    NSMutableArray *array = [NSMutableArray array];
//    while ([set next]) {
//        NSMutableDictionary *dic = [NSMutableDictionary dictionary];
//        [dic setObject:[set stringForColumn:@"userid"] forKey:@"userid"];
//        [dic setObject:[set stringForColumn:@"courseid"] forKey:@"courseid"];
//        [dic setObject:[set stringForColumn:@"courseName"] forKey:@"courseName"];
//        [dic setObject:[set stringForColumn:@"coursePic"] forKey:@"coursePic"];
//        [dic setObject:[set stringForColumn:@"mediaid"] forKey:@"mediaid"];
//        [dic setObject:[set stringForColumn:@"mediaName"] forKey:@"mediaName"];
//        [array addObject:dic];
//    }
//    return array;
//}


#pragma mark---系统通知消息表
//创建表
- (void)creatSystemNewsTableWithUserid:(NSString *)userid{
    NSString *createSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS SystemNewsTable%@(messagekey TEXT PRIMARY KEY,  pushtype TEXT,sendtime TEXT, content TEXT, readed TEXT ,messageid TEXT)",userid];
    [self executeSQL:createSQL message:@"创建表"];
}

//插入数据
- (BOOL)insertSystemNewsWithDic:(NSDictionary *)dic sendTime:(NSString *)sendTime messageid:(NSString *)messageid userid:(NSString *)userid{
    NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO SystemNewsTable%@(messagekey, pushtype, sendtime, content, readed, messageid) VALUES('%@','%@','%@','%@','N','%@')",userid, dic[@"messagekey"], dic[@"pushtype"],sendTime ,[ObjectTool objectToJson:dic],messageid];
    return [self executeSQL:insertSQL message:[NSString stringWithFormat:@"INSERT SystemNewsTable%@",userid]];
}


//更新状态未读、内容、时间戳、ID WHERE：修改标中某个id的数据
- (BOOL)updateSystemNewsWithDic:(NSDictionary *)dic sendTime:(NSString *)sendTime Messagekey:(NSString *)messagekey messageid:(NSString *)messageid userid:(NSString *)userid{
    NSString *updateSQL = [NSString stringWithFormat:@"UPDATE SystemNewsTable%@ SET readed ='N' , content = '%@' , sendtime = '%@' , messageid='%@' WHERE messagekey = '%@'",userid ,[ObjectTool objectToJson:dic],sendTime,messageid,messagekey];
    return [self executeSQL:updateSQL message:[NSString stringWithFormat:@"UPDATE SystemNewsTable%@",userid]];
}

//删除对应messagekey的通知
- (BOOL)deleteSystemNewsWithMessagekey:(NSString *)messagekey userid:(NSString *)userid{
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM SystemNewsTable%@ WHERE messagekey = '%@'",userid,messagekey];
    return [self executeSQL:deleteSQL message:[NSString stringWithFormat:@"DELETE SystemNewsTable%@",userid]];
}

//删除所有通知
- (BOOL)deleteSystemNewsWithUserid:(NSString *)userid{
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM SystemNewsTable%@",userid];
    return [self executeSQL:deleteSQL message:[NSString stringWithFormat:@"DELETE SystemNewsTable%@",userid]];
}

//更新已读
- (BOOL)updateSystemNewsWithMessagekey:(NSString *)messagekey userid:(NSString *)userid{
    NSString *updateSQL = [NSString stringWithFormat:@"UPDATE SystemNewsTable%@ SET readed='Y' WHERE messagekey = '%@'",userid,messagekey];
    return [self executeSQL:updateSQL message:[NSString stringWithFormat:@"UPDATE SystemNewsTable%@",userid]];
}

- (NSMutableArray *)selectSystemNewsWithReaded:(NSString *)Readed userid:(NSString *)userid{
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM SystemNewsTable%@  WHERE readed = '%@'ORDER BY sendtime DESC",userid,Readed];
    FMResultSet *set = [_db executeQuery:selectSQL];
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        //后台通知bug会发送两分消息，一分会是空，这里排出显示数量
        NSString * messagekey = [set stringForColumn:@"messagekey"] ;
        NSString * pushtype = [set stringForColumn:@"pushtype"] ;
//        if ([self isBoolMessagekey:messagekey pushtype:pushtype] == YES) {
//            NSMutableDictionary *dic = [NSMutableDictionary dictionary];
//            [dic setObject:[set stringForColumn:@"messagekey"] forKey:@"messagekey"];
//            [dic setObject:[set stringForColumn:@"pushtype"] forKey:@"pushtype"];
//            [dic setObject:[set stringForColumn:@"content"] forKey:@"content"];
//            [dic setObject:[set stringForColumn:@"readed"] forKey:@"readed"];
//            [dic setObject:[set stringForColumn:@"sendtime"] forKey:@"sendtime"];
//            [dic setObject:[set stringForColumn:@"messageid"] forKey:@"messageid"];
//            [array addObject:dic];
//        }
    }
    return array;
}

- (NSDictionary *)selectSystemNewsWithMessageid:(NSString *)messageid userid:(NSString *)userid{
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM SystemNewsTable%@  WHERE messageid = '%@'",userid,messageid];
    FMResultSet *set = [_db executeQuery:selectSQL];
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        NSString * messagekey = [set stringForColumn:@"messagekey"] ;
        NSString * pushtype = [set stringForColumn:@"pushtype"] ;
//        if ([self isBoolMessagekey:messagekey pushtype:pushtype] == YES) {
//            NSMutableDictionary *dic = [NSMutableDictionary dictionary];
//            [dic setObject:[set stringForColumn:@"messagekey"] forKey:@"messagekey"];
//            [dic setObject:[set stringForColumn:@"pushtype"] forKey:@"pushtype"];
//            [dic setObject:[set stringForColumn:@"content"] forKey:@"content"];
//            [dic setObject:[set stringForColumn:@"readed"] forKey:@"readed"];
//            [dic setObject:[set stringForColumn:@"sendtime"] forKey:@"sendtime"];
//            [dic setObject:[set stringForColumn:@"messageid"] forKey:@"messageid"];
//            [array addObject:dic];
//        }
    }
    if (array.count>0) {
        return array[0];
    }else{
        return nil;
    }
}

- (NSDictionary *)selectSystemNewsWithMessagekey:(NSString *)messagekey userid:(NSString *)userid{
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM SystemNewsTable%@  WHERE messagekey = '%@'",userid,messagekey];
    FMResultSet *set = [_db executeQuery:selectSQL];
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        NSString * messagekey = [set stringForColumn:@"messagekey"] ;
        NSString * pushtype = [set stringForColumn:@"pushtype"] ;
//        if ([self isBoolMessagekey:messagekey pushtype:pushtype] == YES) {
//            NSMutableDictionary *dic = [NSMutableDictionary dictionary];
//            [dic setObject:[set stringForColumn:@"messagekey"] forKey:@"messagekey"];
//            [dic setObject:[set stringForColumn:@"pushtype"] forKey:@"pushtype"];
//            [dic setObject:[set stringForColumn:@"content"] forKey:@"content"];
//            [dic setObject:[set stringForColumn:@"readed"] forKey:@"readed"];
//            [dic setObject:[set stringForColumn:@"sendtime"] forKey:@"sendtime"];
//            [dic setObject:[set stringForColumn:@"messageid"] forKey:@"messageid"];
//            [array addObject:dic];
//        }
    }
    if (array.count>0) {
        return array[0];
    }else{
        return nil;
    }
    
}

- (NSMutableArray *)selectAllSystemNewsUserid:(NSString *)userid{
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM SystemNewsTable%@ ORDER BY sendtime DESC",userid];
    FMResultSet *set = [_db executeQuery:selectSQL];
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        NSString * messagekey = [set stringForColumn:@"messagekey"] ;
        NSString * pushtype = [set stringForColumn:@"pushtype"] ;
//        if ([self isBoolMessagekey:messagekey pushtype:pushtype] == YES) {
//            NSMutableDictionary *dic = [NSMutableDictionary dictionary];
//            [dic setObject:[set stringForColumn:@"messagekey"] forKey:@"messagekey"];
//            [dic setObject:[set stringForColumn:@"pushtype"] forKey:@"pushtype"];
//            [dic setObject:[set stringForColumn:@"content"] forKey:@"content"];
//            [dic setObject:[set stringForColumn:@"readed"] forKey:@"readed"];
//            [dic setObject:[set stringForColumn:@"sendtime"] forKey:@"sendtime"];
//            [dic setObject:[set stringForColumn:@"messageid"] forKey:@"messageid"];
//            [array addObject:dic];
//        }
    }
    return array;
}




#pragma mark---我的通知消息表
- (void)creatMyNewsTableWithUserid:(NSString *)userid{
    NSString *createSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS MyNewsTable%@(messagekey TEXT PRIMARY KEY,  pushtype TEXT,sendtime TEXT, content TEXT, readed TEXT,messageid TEXT)",userid];
    [self executeSQL:createSQL message:@"创建表"];
    
}

//messagekey : 
- (BOOL)insertMyNewsWithDic:(NSDictionary *)dic sendTime:(NSString *)sendTime messageid:(NSString *)messageid userid:(NSString *)userid{
    NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO MyNewsTable%@(messagekey, pushtype, sendtime, content, readed, messageid) VALUES('%@','%@','%@','%@','N','%@')",userid, dic[@"messagekey"], dic[@"pushtype"],sendTime ,[ObjectTool objectToJson:dic],messageid];
    return [self executeSQL:insertSQL message:[NSString stringWithFormat:@"INSERT MyNewsTable%@",userid]];
    
}
- (BOOL)deleteMyNewsWithMessagekey:(NSString *)messagekey userid:(NSString *)userid{
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM MyNewsTable%@ WHERE messagekey = '%@'",userid,messagekey];
    return [self executeSQL:deleteSQL message:[NSString stringWithFormat:@"DELETE MyNewsTable%@",userid]];
}
- (BOOL)updateMyNewsWithMessagekey:(NSString *)messagekey userid:(NSString *)userid{
    NSString *updateSQL = [NSString stringWithFormat:@"UPDATE MyNewsTable%@ SET readed='Y' WHERE messagekey = '%@'",userid,messagekey];
    return [self executeSQL:updateSQL message:[NSString stringWithFormat:@"UPDATE MyNewsTable%@",userid]];
}



- (NSMutableArray *)selectMyNewsWithReaded:(NSString *)Readed userid:(NSString *)userid{
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM MyNewsTable%@  WHERE readed = '%@'ORDER BY sendtime DESC",userid,Readed];
    FMResultSet *set = [_db executeQuery:selectSQL];
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        NSString * messagekey = [set stringForColumn:@"messagekey"] ;
        NSString * pushtype = [set stringForColumn:@"pushtype"] ;
//        if ([self isBoolMessagekey:messagekey pushtype:pushtype] == YES) {
//            NSMutableDictionary *dic = [NSMutableDictionary dictionary];
//            [dic setObject:[set stringForColumn:@"messagekey"] forKey:@"messagekey"];
//            [dic setObject:[set stringForColumn:@"pushtype"] forKey:@"pushtype"];
//            [dic setObject:[set stringForColumn:@"content"] forKey:@"content"];
//            [dic setObject:[set stringForColumn:@"readed"] forKey:@"readed"];
//            [dic setObject:[set stringForColumn:@"sendtime"] forKey:@"sendtime"];
//            [dic setObject:[set stringForColumn:@"messageid"] forKey:@"messageid"];
//            [array addObject:dic];
//        }
    }
    return array;
}

- (NSDictionary *)selectMyNewsWithMessageid:(NSString *)messageid  userid:(NSString *)userid{
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM MyNewsTable%@  WHERE messageid = '%@'",userid,messageid];
    FMResultSet *set = [_db executeQuery:selectSQL];
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        NSString * messagekey = [set stringForColumn:@"messagekey"] ;
        NSString * pushtype = [set stringForColumn:@"pushtype"] ;
//        if ([self isBoolMessagekey:messagekey pushtype:pushtype] == YES) {
//            NSMutableDictionary *dic = [NSMutableDictionary dictionary];
//            [dic setObject:[set stringForColumn:@"messagekey"] forKey:@"messagekey"];
//            [dic setObject:[set stringForColumn:@"pushtype"] forKey:@"pushtype"];
//            [dic setObject:[set stringForColumn:@"content"] forKey:@"content"];
//            [dic setObject:[set stringForColumn:@"readed"] forKey:@"readed"];
//            [dic setObject:[set stringForColumn:@"sendtime"] forKey:@"sendtime"];
//            [dic setObject:[set stringForColumn:@"messageid"] forKey:@"messageid"];
//            [array addObject:dic];
//        }
    }
    if (array.count>0) {
        return array[0];
    }else{
        return nil;
    }
    
}

- (NSMutableArray *)selectAllMyNewsUserid:(NSString *)userid{
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM MyNewsTable%@ ORDER BY sendtime DESC",userid];
    FMResultSet *set = [_db executeQuery:selectSQL];
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        NSString * messagekey = [set stringForColumn:@"messagekey"] ;
        NSString * pushtype = [set stringForColumn:@"pushtype"] ;
//        if ([self isBoolMessagekey:messagekey pushtype:pushtype] == YES) {
//            NSMutableDictionary *dic = [NSMutableDictionary dictionary];
//            [dic setObject:[set stringForColumn:@"messagekey"] forKey:@"messagekey"];
//            [dic setObject:[set stringForColumn:@"pushtype"] forKey:@"pushtype"];
//            [dic setObject:[set stringForColumn:@"content"] forKey:@"content"];
//            [dic setObject:[set stringForColumn:@"readed"] forKey:@"readed"];
//            [dic setObject:[set stringForColumn:@"sendtime"] forKey:@"sendtime"];
//            [dic setObject:[set stringForColumn:@"messageid"] forKey:@"messageid"];
//            [array addObject:dic];
//        }
    }
    return array;
}




// 封装 执行更新
- (BOOL)executeSQL:(NSString *)sql message:(NSString *)message{
    BOOL result = [_db executeUpdate:sql];
    return result;
}


@end
